import requests
import re
import pymysql

# 建立链接
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='mysql', database='qiushi', charset='utf8')
# 选择执行sql语句的游标对象
cursor = conn.cursor()
# sql1 = """create table joke(
#         id int primary key auto_increment not null,
#         name varchar(50),
#         body varchar(500)
# )"""
# cursor.execute(sql1)
for page in range(1, 321):
    url = f'https://qiushidabaike.com/index_{str(page)}.html'
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36'
    }
    response = requests.get(url=url, headers=headers).text
    body_data = '<dl class="main-list">.*?<p>(.*?)</p>.*?</dl>'
    name_data = '<dl class="main-list">.*?<a target="_blank" href=".*?">(.*?)</a>.*?</dl>'
    # 主内容
    body_result = re.compile(body_data, re.S).findall(response)
    # 名字
    name_result = re.compile(name_data, re.S).findall(response)
    for i in range(len(body_result)):
        name = name_result[i]
        body = body_result[i].replace('<br/>', '')[0].replace(r'\n', '')[0]
        cursor.executemany('insert into joke(name,body) values(%s,%s)', [(name, body)])
        print('写入成功')
        conn.commit()
cursor.close()
conn.close()
